<?php

    include_once '../connection/databaseHandler.php';
    include_once '../connection/sessionHandler.php';
    $database   = new DatabaseHandler();
    $session    = new SessionHandler();

    $dbchat         = $database->t_chat;
    $dbchatfor      = $database->t_chatfor;
    $dbchatreceived = $database->t_chatreceived;

    // cek dahulu apakah last received message telah terdaftar pada database
    // kalau belum, tambahkan
    $query = "SELECT * FROM ".$dbchatreceived." WHERE username='".$session->username."'";
    $result = $database->execQuery($query);
    $count = mysql_num_rows($result);
    if ($count==0) {
        // masukkan data dummy ke database chatreceived
        $query = "INSERT INTO ".$dbchatreceived." (username,timestamp) VALUE ('".$session->username."','0000-00-00 00:00:00')";
        $result = $database->execQuery($query);
    }

    // mengambil pesan yang ada
    $query =
//    "SELECT * FROM ".$dbchat."
//        LEFT JOIN ".$dbchatfor." ON ".$dbchat.".id = ".$dbchatfor.".id_chat
//        WHERE (".$dbchatfor.".to_username='".$session->username."' OR ".$dbchat.".username='".$session->username."')
//        ORDER BY ".$dbchat.".date ASC";
//    "SELECT * FROM ".$dbchat."
//        INNER JOIN ".$dbchatfor." ON ".$dbchat.".id = ".$dbchatfor.".id_chat
//        INNER JOIN ".$dbchatreceived." ON (".$dbchat.".username = ".$dbchatreceived.".username )
//        WHERE ((".$dbchatfor.".to_username='".$session->username."' OR ".$dbchat.".username='".$session->username."') AND ".$dbchat.".date>=".$dbchatreceived.".timestamp)
//        ORDER BY ".$dbchat.".date DESC";
     "SELECT * FROM ".$dbchat."
        INNER JOIN ".$dbchatfor." ON ".$dbchat.".id = ".$dbchatfor.".id_chat
        WHERE ((".$dbchatfor.".to_username='".$session->username."' OR ".$dbchat.".username='".$session->username."') AND ".$dbchat.".date>=(SELECT timestamp FROM ".$dbchatreceived." WHERE username='".$session->username."'))
        ORDER BY ".$dbchat.".date ASC";
    $result = $database->execQuery($query);
    $count  = mysql_num_rows($result);

    header("Content-type: text/xml");
    $xmlResponse = "<root><cuser>".$session->username."</cuser>";
    
    for ($i=0;$i<$count;$i++) {
        $xmlResponse .= "<package>";
        $xmlResponse .= "<from>".mysql_result($result, $i, $dbchat.".username")."</from>";
        $xmlResponse .= "<to>".mysql_result($result, $i, $dbchatfor.".to_username")."</to>";
        $xmlResponse .= "<message>".mysql_result($result, $i, $dbchat.".content")."</message>";
        $xmlResponse .= "</package>";
    }
    $xmlResponse .= "</root>";

    echo $xmlResponse;
/*
    echo mysql_result($result, 0, $dbchat.".id");
    echo mysql_result($result, 0, $dbchat.".username");
    echo mysql_result($result, 0, $dbchat.".content");
    echo mysql_result($result, 0, $dbchat.".date");
    echo mysql_result($result, 0, $dbchatfor.".to_username");
*/
    
?>
